package com.tanhua.admin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.model.domain.Log;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


public interface LogMapper extends BaseMapper<Log> {

    @Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE TYPE=#{type} AND log_time=#{logTime}")
    Integer queryByTypeAndLogTime(@Param("type") String type, @Param("logTime") String logTime); //根据操作时间和类型


    @Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE log_time=#{logTime}")
    Integer queryByLogTime(String logTime); //展示记录时间查询


    @Select("SELECT COUNT(DISTINCT user_id)  FROM tb_log WHERE log_time=#{today} AND user_id IN (\n " +
            " SELECT user_id FROM tb_log WHERE TYPE=\"0102\" AND log_time=#{yestoday} \n " +
            ")")
    Integer queryNumRetention1d(@Param("today") String today,@Param("yestoday") String yestoday); //查询次日留存

    /**
     * 查询注册/登录用户数
     * 根据日志类型统计当前日期当前类型日志总数（如：注册，登录）
     * eySQL : select COUNT(DISTINCT user_id) from tb_log where type = 01 and log_time = "2020-09-25";
     * @param type 日志类型
     * @param today 当前日志
     * @return
     */
    @Select("select count(DISTINCT user_id) from tb_log where type = #{type} and log_time = #{today}")
    Integer countByTypeAndDate(@Param("type")String type,@Param("today") String today);

    /**
     * 查询活跃用户数
     * 根据指定日期查询当天用户活跃数量 ，单个用户的多个类型日志记为一次活跃数（根据userID去重）
     * eySQL:select count(DISTINCT user_id) from tb_log where log_time = "2020-09-25";
     * @param today
     * @return
     */
    @Select("select count(distinct user_id) from tb_log where log_time =#{today}")
    Integer countActiveByDate(@Param("today") String today);

    /**
     * 查询残留用户数
     * 统计残留用户数，即从昨日活跃用户id中查询在今日活跃用户中的数量
     * eySQL : select count(DISTINCT user_id) from tb_log where log_time = "2020-09-26"
     * and user_id in(select DISTINCT user_id from tb_log where log_time="2020-09-25");
     * @param today
     * @param yesterday
     * @return
     */
    @Select("select count(distinct user_id) from tb_log where log_time =#{today} " +
            "and user_id in(select distinct user_id from tb_log where log_time=#{yesterday})")
    Integer countRetention1dUser(@Param("today") String today, @Param("yesterday") String yesterday);


    /**
     * 累计用户查询
     * eySQL:select count(distinct user_id) from tb_log;
     * @return
     */
    @Select("select count(distinct user_id) from tb_log")
    Integer summaryUserNum();

    /**
     * 查询指定日期之前的活跃用户数量 每个用户只计一次
     * @param endDate
     * @param fromDate
     * @return
     */
    @Select("select count(distinct user_id) from tb_log where log_time between #{fromDate} and #{endDate} ")
    Integer betweenDateActiveUser(@Param("endDate") String endDate,@Param("fromDate") String fromDate);

}
